package com.py.credit.controller;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.lang.reflect.Array;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.commons.fileupload.FileItem;
import org.apache.commons.fileupload.FileItemFactory;
import org.apache.commons.fileupload.disk.DiskFileItemFactory;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.jsoup.helper.DataUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.commons.CommonsMultipartFile;

import com.py.credit.common.ServiceException;
import com.py.credit.common.logs.ExceptionLogger;
import com.py.credit.common.rest.RestServiceResult;

import com.py.credit.entity.SysUser;
import com.py.credit.entity.Table;
import com.py.credit.entity.XzcfEntity;
import com.py.credit.entity.XzxkEntity;
import com.py.credit.service.BatchFeedbackService;
import com.py.credit.service.BatchLogService;
import com.py.credit.service.RemoteBaseService;
import com.py.credit.service.StepLogService;
import com.py.credit.service.TableService;
import com.py.credit.service.impl.UntreatedFileShedule;
import com.py.credit.utils.DateUtils;
import com.py.framework.core.annotation.IgnoreAuthority;
import com.py.framework.core.helper.HttpServletHelper;

/**
 * 文件导入日志
 * 
 * 
 */
@RestController
@RequestMapping(value = "/batchLog")
public class BatchLogSplitController extends BaseController {

	private static final String EXCEL_XLS = "xls";
	private static final String EXCEL_XLSX = "xlsx";
	private final static String FRONT_TABLE_ID = "batch";

	@Autowired
	private BatchLogService batchLogService;

	@Autowired
	private StepLogService stepLogService;

	@Autowired
	private TableService tableService;

	@Value("${absolutepath}")
	private String absolutepath;

	@Autowired
	private RemoteBaseService remoteBaseService;

	@Autowired
	private UntreatedFileShedule untreatedFileShedule;

	@Autowired
	private BatchFeedbackService batchFeedbackService;

	/**
	 * @throws IOException
	 * @throws FileNotFoundException
	 * @api {get} /batchLog/doFile.json 上传附件
	 * @apiName doUploadMultiFile
	 * @apiVersion 0.0.1
	 * @apiGroup 数据采集
	 * 
	 * @apiSuccess {CommonsMultipartFile} file 文件
	 * @apiSuccess {String} tableId 表的id
	 * @apiSuccess {String} msg 错误信息
	 * @apiSuccess {int} count 结果集数据量
	 * @apiSuccess {Object[]} data 结果集
	 *
	 * @apiSuccessExample {json} 成功返回结果 {"code":0,"msg":null,"count":0,"data":[]}
	 * 
	 */
	@IgnoreAuthority
	@ResponseBody
	@RequestMapping(value = "/SplitdoFile.json")
	public RestServiceResult<Object> doUploadMultiFile(@RequestParam(value = "file") CommonsMultipartFile file,
			@RequestParam(value = "type", required = true) String type, HttpServletRequest request,
			HttpServletResponse response) throws FileNotFoundException, IOException {
		RestServiceResult<Object> result = new RestServiceResult<Object>();
		String batchId = null;

		// 获取当前用户信息
		RestServiceResult<SysUser> user = remoteBaseService.getCurrentUser(HttpServletHelper.getUserToken(request));
		SysUser userInfo = null;
		if (RestServiceResult.CODE_SUCCESS == user.getCode()) {
			userInfo = user.getData();
			if (null == userInfo || userInfo.getId() <= 0) {
				result.setCode(RestServiceResult.CODE_UNKNOW);
				result.setMsg("系统异常，请稍后再试！");
				ExceptionLogger.error("BatchLogController#doUploadMultiFile", "未获取到当前用户信息！");
				return result;
			}
		} else {
			result.setCode(user.getCode());
			result.setMsg(user.getMsg());
			return result;
		}
		Table table = null;

		// 获取文件的后缀
		String fileType = null;
		if (file != null) {
			// 获取文件后缀
			if (file.getOriginalFilename().contains(".")) {
				fileType = file.getOriginalFilename().substring(file.getOriginalFilename().lastIndexOf("."),
						file.getOriginalFilename().length());
			}
		} else {
			throw new ServiceException("上传的文件不得为空");// 抛出异常
		}
		if (!".xls".equalsIgnoreCase(fileType) && !".xlsx".equalsIgnoreCase(fileType)) {
			throw new ServiceException("上传的文件格式不支持。");
		}
		try {
			if (type.equals("许可")) {
				// 读取excel所有记录数
				List<List<String>> lists = readExcel(file);
				XzxkEntity frentity = new XzxkEntity();
				List<XzxkEntity> ListAll = new ArrayList<>();
				List<XzxkEntity> frdata = new ArrayList<>();
				List<XzxkEntity> zrrdata = new ArrayList<>();
				List<String> fr = new ArrayList<>();
				lists.remove(0);
				for (int k = 0; k < lists.size(); k++) {
					frentity = new XzxkEntity();
					fr = lists.get(k);
					frentity.setA000001(fr.get(0));
					frentity.setA000002(fr.get(1));
					frentity.setA000003(fr.get(2));
					frentity.setA000004(fr.get(3));
					frentity.setA000005(fr.get(4));
					frentity.setXK_XDR_SYDW(fr.get(5));
					frentity.setXK_XDR_SHZZ(fr.get(6));
					frentity.setXK_FDDB(fr.get(7));
					frentity.setXK_FDDBRZJLX(fr.get(8));
					frentity.setXK_FDDBRZJHM(fr.get(9));
					frentity.setXZXDRLB(fr.get(10));
					frentity.setZJLX(fr.get(11));
					frentity.setZJHM(fr.get(12));
					frentity.setXK_XKWS(fr.get(13));
					frentity.setXK_WSH(fr.get(14));
					frentity.setXKLB(fr.get(15));
					
					frentity.setXK_XKZS(fr.get(16));
					frentity.setXK_XKBH(fr.get(17));
					frentity.setXK_NR(fr.get(18));
					frentity.setXK_JDRQ(fr.get(19));
					frentity.setXK_YXQZ(fr.get(20));
					frentity.setXK_YXQZI(fr.get(21));
					frentity.setXK_XKJG(fr.get(22));
					frentity.setXK_XKJGDM(fr.get(23));
					frentity.setXK_ZT(fr.get(24));
					frentity.setXK_LYDW(fr.get(25));
					frentity.setXK_LYDWDM(fr.get(26));
					frentity.setBZ(fr.get(27));
					ListAll.add(frentity);
				}
				for (XzxkEntity entity : ListAll) {
					if (entity.getXZXDRLB().equals("法人及非法人组织")||entity.getXZXDRLB().equals("个体工商户")) {
						// 获取excel中所有法人数据
						frdata.add(entity);
					} else if (entity.getXZXDRLB().equals("自然人")) {
						// 获取excel中所有自然人数据
						zrrdata.add(entity);
					}
				}
				if (frdata.size() > 0) {
					table = tableService.queryCubeById(210000);
					// 导出法人行政许可
					HSSFWorkbook workbook = null;

					// 产生工作簿对象
					workbook = new HSSFWorkbook();

					// 产生工作表对象
					HSSFSheet sheet = workbook.createSheet();
					workbook.setSheetName(0, "法人行政许可");
					HSSFRow row = null;
					HSSFCell cell = null;

					String[] frcell = { "行政相对人名称", "行政相对人类别", "统一社会信用代码", "工商注册号", "组织机构代码", "税务登记号", "事业单位证书号",
							"社会组织登记证号", "法定代表人", "法定代表人证件类型", "法定代表人证件号码", "行政许可决定文书名称", "行政许可决定文书号", "许可类别",
							"其他类别（内容）", "许可证书名称", "许可编号", "许可内容", "许可决定日期", "有效期自", "有效期至", "许可机关", "许可机关统一社会信用代码",
							"当前状态", "数据来源单位", "数据来源单位统一社会信用代码", "备注" };
					// 创建标题行
					row = sheet.createRow(0);
					for (int i = 0; i < frcell.length; i++) {
						cell = row.createCell(i);
						cell.setCellType(HSSFCell.CELL_TYPE_STRING);
						cell.setCellValue(frcell[i]);
					}
					for (int j = 0; j < frdata.size(); j++) {
						row = sheet.createRow(j + 1);// 创建一行
						cell = row.createCell(0);
						cell.setCellType(HSSFCell.CELL_TYPE_STRING);
						cell.setCellValue((String) frdata.get(j).getA000001());

						cell = row.createCell(1);
						cell.setCellType(HSSFCell.CELL_TYPE_STRING);
						cell.setCellValue((String) frdata.get(j).getXZXDRLB());

						cell = row.createCell(2);
						cell.setCellType(HSSFCell.CELL_TYPE_STRING);
						cell.setCellValue((String) frdata.get(j).getA000002());

						cell = row.createCell(3);
						cell.setCellType(HSSFCell.CELL_TYPE_STRING);
						cell.setCellValue((String) frdata.get(j).getA000003());

						cell = row.createCell(4);
						cell.setCellType(HSSFCell.CELL_TYPE_STRING);
						cell.setCellValue(frdata.get(j).getA000004());

						cell = row.createCell(5);
						cell.setCellType(HSSFCell.CELL_TYPE_STRING);
						cell.setCellValue(frdata.get(j).getA000005());

						cell = row.createCell(6);
						cell.setCellType(HSSFCell.CELL_TYPE_STRING);
						cell.setCellValue(frdata.get(j).getXK_XDR_SYDW());

						cell = row.createCell(7);
						cell.setCellType(HSSFCell.CELL_TYPE_STRING);
						cell.setCellValue(frdata.get(j).getXK_XDR_SHZZ());

						cell = row.createCell(8);
						cell.setCellType(HSSFCell.CELL_TYPE_STRING);
						cell.setCellValue(frdata.get(j).getXK_FDDB());

						cell = row.createCell(9);
						cell.setCellType(HSSFCell.CELL_TYPE_STRING);
						cell.setCellValue(frdata.get(j).getXK_FDDBRZJLX());

						cell = row.createCell(10);
						cell.setCellType(HSSFCell.CELL_TYPE_STRING);
						cell.setCellValue(frdata.get(j).getXK_FDDBRZJHM());

						cell = row.createCell(11);
						cell.setCellType(HSSFCell.CELL_TYPE_STRING);
						cell.setCellValue(frdata.get(j).getXK_XKWS());

						cell = row.createCell(12);
						cell.setCellType(HSSFCell.CELL_TYPE_STRING);
						cell.setCellValue(frdata.get(j).getXK_WSH());

						cell = row.createCell(13);
						cell.setCellType(HSSFCell.CELL_TYPE_STRING);
						cell.setCellValue(frdata.get(j).getXKLB());

						cell = row.createCell(14);
						cell.setCellType(HSSFCell.CELL_TYPE_STRING);
						cell.setCellValue(frdata.get(j).getXKLB());

						cell = row.createCell(15);
						cell.setCellType(HSSFCell.CELL_TYPE_STRING);
						cell.setCellValue(frdata.get(j).getXK_XKZS());

						cell = row.createCell(16);
						cell.setCellType(HSSFCell.CELL_TYPE_STRING);
						cell.setCellValue(frdata.get(j).getXK_XKBH());

						cell = row.createCell(17);
						cell.setCellType(HSSFCell.CELL_TYPE_STRING);
						cell.setCellValue(frdata.get(j).getXK_NR());

						cell = row.createCell(18);
						cell.setCellType(HSSFCell.CELL_TYPE_STRING);
						cell.setCellValue(frdata.get(j).getXK_JDRQ());

						cell = row.createCell(19);
						cell.setCellType(HSSFCell.CELL_TYPE_STRING);
						cell.setCellValue(frdata.get(j).getXK_YXQZ());

						cell = row.createCell(20);
						cell.setCellType(HSSFCell.CELL_TYPE_STRING);
						cell.setCellValue(frdata.get(j).getXK_YXQZI());

						cell = row.createCell(21);
						cell.setCellType(HSSFCell.CELL_TYPE_STRING);
						cell.setCellValue(frdata.get(j).getXK_XKJG());

						cell = row.createCell(22);
						cell.setCellType(HSSFCell.CELL_TYPE_STRING);
						cell.setCellValue(frdata.get(j).getXK_XKJGDM());

						cell = row.createCell(23);
						cell.setCellType(HSSFCell.CELL_TYPE_STRING);
						cell.setCellValue(frdata.get(j).getXK_ZT());

						cell = row.createCell(24);
						cell.setCellType(HSSFCell.CELL_TYPE_STRING);
						cell.setCellValue(frdata.get(j).getXK_LYDW());

						cell = row.createCell(25);
						cell.setCellType(HSSFCell.CELL_TYPE_STRING);
						cell.setCellValue(frdata.get(j).getXK_LYDWDM());

						cell = row.createCell(26);
						cell.setCellType(HSSFCell.CELL_TYPE_STRING);
						cell.setCellValue(frdata.get(j).getBZ());
					}

					// 6.将其生成一个excel文件，输出
					String name = absolutepath + File.separator + DateUtils.getCurrentTimeNumber() + "法人行政许可.xls";
					File files = new File(name);
					try {
						// 在本地生成一个excel文件，在传

						FileOutputStream file1 = new FileOutputStream(files);
						workbook.write(file1);
						file1.close();
					} catch (UnsupportedEncodingException e) {
						e.printStackTrace();
					}
					FileItem fileItem = createFileItem(files, "法人行政许可");
					CommonsMultipartFile frfile = new CommonsMultipartFile(fileItem);
					table.setRootId(100000);
					batchId = batchLogService.doUploadFile(table, frfile, userInfo, absolutepath);
					// 添加处理流程日志
					stepLogService.addStepLog(batchId, "文件上传完成,请耐心等待处理结果。");
					files.delete();
				}
				if (zrrdata.size() > 0) {
					table = tableService.queryCubeById(210002);
					// 导出自然人行政许可
					HSSFWorkbook zrrworkbook = null;
					zrrworkbook = new HSSFWorkbook();
					// 产生工作表对象
					HSSFSheet zrrsheet = zrrworkbook.createSheet();
					zrrworkbook.setSheetName(0, "自然人人行政许可");
					HSSFRow zrrrow = null;
					HSSFCell cell2 = null;

					String[] zrrcell = { "行政相对人名称", "居民身份证号", "行政相对人类别", "住址", "证件类型", "行政许可决定文书名称", "行政许可决定书文号",
							"许可类别", "其他类别（内容）", "许可证书名称", "许可编号", "许可内容", "许可决定日期", "有效期自", "有效期至", "许可机关",
							"许可机关统一社会信用代码", "当前状态", "数据来源单位", "数据来源单位统一社会信用代码", "备注" };
					// 创建标题行
					zrrrow = zrrsheet.createRow(0);
					for (int i = 0; i < zrrcell.length; i++) {
						cell2 = zrrrow.createCell(i);
						cell2.setCellType(HSSFCell.CELL_TYPE_STRING);
						cell2.setCellValue(zrrcell[i]);
					}
					for (int j = 0; j < zrrdata.size(); j++) {
						zrrrow = zrrsheet.createRow(j + 1);// 创建一行
						cell2 = zrrrow.createCell(0);
						cell2.setCellType(HSSFCell.CELL_TYPE_STRING);
						cell2.setCellValue((String) zrrdata.get(j).getA000001());

						cell2 = zrrrow.createCell(1);
						cell2.setCellType(HSSFCell.CELL_TYPE_STRING);
						cell2.setCellValue((String) zrrdata.get(j).getZJHM());

						cell2 = zrrrow.createCell(2);
						cell2.setCellType(HSSFCell.CELL_TYPE_STRING);
						cell2.setCellValue((String) zrrdata.get(j).getXZXDRLB());

						cell2 = zrrrow.createCell(3);
						cell2.setCellType(HSSFCell.CELL_TYPE_STRING);
						cell2.setCellValue("无数据");

						cell2 = zrrrow.createCell(4);
						cell2.setCellType(HSSFCell.CELL_TYPE_STRING);
						cell2.setCellValue(zrrdata.get(j).getZJLX());

						cell2 = zrrrow.createCell(5);
						cell2.setCellType(HSSFCell.CELL_TYPE_STRING);
						cell2.setCellValue(zrrdata.get(j).getXK_XKWS());

						cell2 = zrrrow.createCell(6);
						cell2.setCellType(HSSFCell.CELL_TYPE_STRING);
						cell2.setCellValue(zrrdata.get(j).getXK_WSH());

						cell2 = zrrrow.createCell(7);
						cell2.setCellType(HSSFCell.CELL_TYPE_STRING);
						cell2.setCellValue(zrrdata.get(j).getXKLB());

						cell2 = zrrrow.createCell(8);
						cell2.setCellType(HSSFCell.CELL_TYPE_STRING);
						cell2.setCellValue(zrrdata.get(j).getXKLB());

						cell2 = zrrrow.createCell(9);
						cell2.setCellType(HSSFCell.CELL_TYPE_STRING);
						cell2.setCellValue(zrrdata.get(j).getXK_XKZS());

						cell2 = zrrrow.createCell(10);
						cell2.setCellType(HSSFCell.CELL_TYPE_STRING);
						cell2.setCellValue(zrrdata.get(j).getXK_XKBH());

						cell2 = zrrrow.createCell(11);
						cell2.setCellType(HSSFCell.CELL_TYPE_STRING);
						cell2.setCellValue(zrrdata.get(j).getXK_NR());

						cell2 = zrrrow.createCell(12);
						cell2.setCellType(HSSFCell.CELL_TYPE_STRING);
						cell2.setCellValue(zrrdata.get(j).getXK_JDRQ());

						cell2 = zrrrow.createCell(13);
						cell2.setCellType(HSSFCell.CELL_TYPE_STRING);
						cell2.setCellValue(zrrdata.get(j).getXK_YXQZ());

						cell2 = zrrrow.createCell(14);
						cell2.setCellType(HSSFCell.CELL_TYPE_STRING);
						cell2.setCellValue(zrrdata.get(j).getXK_YXQZI());

						cell2 = zrrrow.createCell(15);
						cell2.setCellType(HSSFCell.CELL_TYPE_STRING);
						cell2.setCellValue(zrrdata.get(j).getXK_XKJG());

						cell2 = zrrrow.createCell(16);
						cell2.setCellType(HSSFCell.CELL_TYPE_STRING);
						cell2.setCellValue(zrrdata.get(j).getXK_XKJGDM());

						cell2 = zrrrow.createCell(17);
						cell2.setCellType(HSSFCell.CELL_TYPE_STRING);
						cell2.setCellValue(zrrdata.get(j).getXK_ZT());

						cell2 = zrrrow.createCell(18);
						cell2.setCellType(HSSFCell.CELL_TYPE_STRING);
						cell2.setCellValue(zrrdata.get(j).getXK_LYDW());

						cell2 = zrrrow.createCell(19);
						cell2.setCellType(HSSFCell.CELL_TYPE_STRING);
						cell2.setCellValue(zrrdata.get(j).getXK_LYDWDM());

						cell2 = zrrrow.createCell(20);
						cell2.setCellType(HSSFCell.CELL_TYPE_STRING);
						cell2.setCellValue(zrrdata.get(j).getBZ());
					}

					// 6.将其生成一个excel文件，输出
					String namezrr = absolutepath + File.separator + DateUtils.getCurrentTimeNumber() + "自然人行政许可.xls";
					File filezrr = new File(namezrr);
					try {
						// 在本地生成一个excel文件，在传

						FileOutputStream file2 = new FileOutputStream(filezrr);
						zrrworkbook.write(file2);
						file2.close();
					} catch (UnsupportedEncodingException e) {
						e.printStackTrace();
					}
					FileItem fileItemzrr = createFileItem(filezrr, "自然人行政许可");
					table.setRootId(100001);
					CommonsMultipartFile zrrfile = new CommonsMultipartFile(fileItemzrr);
					batchId = batchLogService.doUploadFile(table, zrrfile, userInfo, absolutepath);
					// 添加处理流程日志
					stepLogService.addStepLog(batchId, "文件上传完成,请耐心等待处理结果。");
					filezrr.delete();
				}
			} else {
				// 处罚数据
				// 读取excel所有记录数
				List<List<String>> lists = readExcel(file);
				XzcfEntity frentity = new XzcfEntity();
				List<XzcfEntity> ListAll = new ArrayList<>();
				List<XzcfEntity> frdata = new ArrayList<>();
				List<XzcfEntity> zrrdata = new ArrayList<>();
				List<String> fr = new ArrayList<>();
				lists.remove(0);
				System.out.println(lists);
				for (int k = 0; k < lists.size(); k++) {
					frentity = new XzcfEntity();
					fr = lists.get(k);
					frentity.setA000001(fr.get(0));
					frentity.setA000002(fr.get(1));
					frentity.setA000003(fr.get(2));
					frentity.setA000004(fr.get(3));
					frentity.setA000005(fr.get(4));
					frentity.setCF_XDR_SYDW(fr.get(5));
					frentity.setCF_XDR_SHZZ(fr.get(6));
					frentity.setCF_FDDB(fr.get(7));
					frentity.setCF_FDDBRZJLX(fr.get(8));
					frentity.setCF_FDDBRZJHM(fr.get(9));
					frentity.setXZXDRLB(fr.get(10));
					frentity.setZJLX(fr.get(11));
					frentity.setZJHM(fr.get(12));
					frentity.setCF_WSH(fr.get(13));
					frentity.setWFXW(fr.get(14));
					frentity.setWFSS(fr.get(15));
					frentity.setCFYJ(fr.get(16));
					frentity.setCFLB(fr.get(17));
					frentity.setCFNR(fr.get(18));
					frentity.setFKJE(fr.get(19));
					frentity.setCF_MSSD(fr.get(20));
					frentity.setCF_ZKDX(fr.get(21));
					frentity.setCF_JDRQ(fr.get(22));
					frentity.setCF_YXQ(fr.get(23));
					frentity.setCF_GSJZQ(fr.get(24));
					frentity.setCFJG(fr.get(25));
					frentity.setCFJGSHXYDM(fr.get(26));
					frentity.setCF_LYDW(fr.get(27));
					frentity.setCF_LYDWDM(fr.get(28));
					frentity.setBZ(fr.get(29));
					ListAll.add(frentity);
				}
				for (XzcfEntity entity : ListAll) {
					if (entity.getXZXDRLB().equals("法人及非法人组织")||entity.getXZXDRLB().equals("个体工商户")) {
						// 获取excel中所有法人数据
						frdata.add(entity);
					} else if (entity.getXZXDRLB().equals("自然人")) {
						// 获取excel中所有自然人数据
						zrrdata.add(entity);
					}
				}
				if (frdata.size() > 0) {
					table = tableService.queryCubeById(210001);
					// 导出法人行政处罚
					HSSFWorkbook workbook = null;

					// 产生工作簿对象
					workbook = new HSSFWorkbook();

					// 产生工作表对象
					HSSFSheet sheet = workbook.createSheet();
					workbook.setSheetName(0, "法人行政处罚");
					HSSFRow row = null;
					HSSFCell cell = null;

					String[] frcell = { "行政相对人名称", "行政相对人类别", "统一社会信用代码", "工商注册号", "组织机构代码", "税务登记号", "事业单位证书号",
							"社会组织登记证号", "法定代表人", "法定代表人证件类型", "法定代表人证件号码", "行政处罚决定书文号", "违法行为类型", "违法事实", "处罚依据",
							"处罚类别", "其他类别（内容）", "处罚内容", "罚款金额（万元）", "没收违法所得、没收非法财物的金额（万元）", "暂扣或吊销证照名称及编号", "处罚决定日期",
							"处罚有效期", "公示截止期", "处罚机关", "处罚机关统一社会信用代码", "数据来源单位", "数据来源单位统一社会信用代码", "备注" };
					// 创建标题行
					row = sheet.createRow(0);
					for (int i = 0; i < frcell.length; i++) {
						cell = row.createCell(i);
						cell.setCellType(HSSFCell.CELL_TYPE_STRING);
						cell.setCellValue(frcell[i]);
					}
					for (int j = 0; j < frdata.size(); j++) {
						row = sheet.createRow(j + 1);// 创建一行
						cell = row.createCell(0);
						cell.setCellType(HSSFCell.CELL_TYPE_STRING);
						cell.setCellValue((String) frdata.get(j).getA000001());

						cell = row.createCell(1);
						cell.setCellType(HSSFCell.CELL_TYPE_STRING);
						cell.setCellValue((String) frdata.get(j).getXZXDRLB());

						cell = row.createCell(2);
						cell.setCellType(HSSFCell.CELL_TYPE_STRING);
						cell.setCellValue((String) frdata.get(j).getA000002());

						cell = row.createCell(3);
						cell.setCellType(HSSFCell.CELL_TYPE_STRING);
						cell.setCellValue((String) frdata.get(j).getA000003());

						cell = row.createCell(4);
						cell.setCellType(HSSFCell.CELL_TYPE_STRING);
						cell.setCellValue(frdata.get(j).getA000004());

						cell = row.createCell(5);
						cell.setCellType(HSSFCell.CELL_TYPE_STRING);
						cell.setCellValue(frdata.get(j).getA000005());

						cell = row.createCell(6);
						cell.setCellType(HSSFCell.CELL_TYPE_STRING);
						cell.setCellValue(frdata.get(j).getCF_XDR_SYDW());

						cell = row.createCell(7);
						cell.setCellType(HSSFCell.CELL_TYPE_STRING);
						cell.setCellValue(frdata.get(j).getCF_XDR_SHZZ());

						cell = row.createCell(8);
						cell.setCellType(HSSFCell.CELL_TYPE_STRING);
						cell.setCellValue(frdata.get(j).getCF_FDDB());

						cell = row.createCell(9);
						cell.setCellType(HSSFCell.CELL_TYPE_STRING);
						cell.setCellValue(frdata.get(j).getCF_FDDBRZJLX());

						cell = row.createCell(10);
						cell.setCellType(HSSFCell.CELL_TYPE_STRING);
						cell.setCellValue(frdata.get(j).getCF_FDDBRZJHM());

						cell = row.createCell(11);
						cell.setCellType(HSSFCell.CELL_TYPE_STRING);
						cell.setCellValue(frdata.get(j).getCF_WSH());

						cell = row.createCell(12);
						cell.setCellType(HSSFCell.CELL_TYPE_STRING);
						cell.setCellValue(frdata.get(j).getWFXW());

						cell = row.createCell(13);
						cell.setCellType(HSSFCell.CELL_TYPE_STRING);
						cell.setCellValue(frdata.get(j).getWFSS());

						cell = row.createCell(14);
						cell.setCellType(HSSFCell.CELL_TYPE_STRING);
						cell.setCellValue(frdata.get(j).getCFYJ());

						cell = row.createCell(15);
						cell.setCellType(HSSFCell.CELL_TYPE_STRING);
						cell.setCellValue(frdata.get(j).getCFLB());

						cell = row.createCell(16);
						cell.setCellType(HSSFCell.CELL_TYPE_STRING);
						cell.setCellValue(frdata.get(j).getCFLB());

						cell = row.createCell(17);
						cell.setCellType(HSSFCell.CELL_TYPE_STRING);
						cell.setCellValue(frdata.get(j).getCFNR());

						cell = row.createCell(18);
						cell.setCellType(HSSFCell.CELL_TYPE_STRING);
						cell.setCellValue(frdata.get(j).getFKJE());

						cell = row.createCell(19);
						cell.setCellType(HSSFCell.CELL_TYPE_STRING);
						cell.setCellValue(frdata.get(j).getCF_MSSD());

						cell = row.createCell(20);
						cell.setCellType(HSSFCell.CELL_TYPE_STRING);
						cell.setCellValue(frdata.get(j).getCF_ZKDX());

						cell = row.createCell(21);
						cell.setCellType(HSSFCell.CELL_TYPE_STRING);
						cell.setCellValue(frdata.get(j).getCF_JDRQ());

						cell = row.createCell(22);
						cell.setCellType(HSSFCell.CELL_TYPE_STRING);
						cell.setCellValue(frdata.get(j).getCF_YXQ());

						cell = row.createCell(23);
						cell.setCellType(HSSFCell.CELL_TYPE_STRING);
						cell.setCellValue(frdata.get(j).getCF_GSJZQ());

						cell = row.createCell(24);
						cell.setCellType(HSSFCell.CELL_TYPE_STRING);
						cell.setCellValue(frdata.get(j).getCFJG());

						cell = row.createCell(25);
						cell.setCellType(HSSFCell.CELL_TYPE_STRING);
						cell.setCellValue(frdata.get(j).getCFJGSHXYDM());

						cell = row.createCell(26);
						cell.setCellType(HSSFCell.CELL_TYPE_STRING);
						cell.setCellValue(frdata.get(j).getCF_LYDW());

						cell = row.createCell(27);
						cell.setCellType(HSSFCell.CELL_TYPE_STRING);
						cell.setCellValue(frdata.get(j).getCF_LYDWDM());

						cell = row.createCell(28);
						cell.setCellType(HSSFCell.CELL_TYPE_STRING);
						cell.setCellValue(frdata.get(j).getBZ());
					}

					// 6.将其生成一个excel文件，输出
					String name = absolutepath + File.separator + DateUtils.getCurrentTimeNumber() + "法人行政处罚.xls";
					File files = new File(name);
					try {
						// 在本地生成一个excel文件，在传

						FileOutputStream file1 = new FileOutputStream(files);
						workbook.write(file1);
						file1.close();
					} catch (UnsupportedEncodingException e) {
						e.printStackTrace();
					}
					FileItem fileItem = createFileItem(files, "法人行政处罚");
					CommonsMultipartFile frfile = new CommonsMultipartFile(fileItem);
					table.setRootId(100000);
					batchId = batchLogService.doUploadFile(table, frfile, userInfo, absolutepath);
					// 添加处理流程日志
					stepLogService.addStepLog(batchId, "文件上传完成,请耐心等待处理结果。");
					files.delete();
				}
				if (zrrdata.size() > 0) {
					table = tableService.queryCubeById(210003);
					// 导出自然人行政处罚
					HSSFWorkbook zrrworkbook = null;
					zrrworkbook = new HSSFWorkbook();
					// 产生工作表对象
					HSSFSheet zrrsheet = zrrworkbook.createSheet();
					zrrworkbook.setSheetName(0, "自然人行政处罚");
					HSSFRow zrrrow = null;
					HSSFCell cell2 = null;

					String[] zrrcell = { "行政相对人名称", "居民身份证号", "行政相对人类别", "住址", "证件类型", "行政处罚决定书文号", "违法行为类型", "违法事实",
							"处罚依据", "处罚类别", "其他类别（内容）", "处罚内容", "罚款金额（万元）", "没收违法所得、没收非法财物的金额（万元）", "暂扣或吊销证照名称及编号",
							"处罚决定日期", "处罚有效期", "公示截止期", "处罚机关", "处罚机关统一社会信用代码", "数据来源单位", "数据来源单位统一社会信用代码", "备注" };
					// 创建标题行
					zrrrow = zrrsheet.createRow(0);
					for (int i = 0; i < zrrcell.length; i++) {
						cell2 = zrrrow.createCell(i);
						cell2.setCellType(HSSFCell.CELL_TYPE_STRING);
						cell2.setCellValue(zrrcell[i]);
					}
					for (int j = 0; j < zrrdata.size(); j++) {
						zrrrow = zrrsheet.createRow(j + 1);// 创建一行
						cell2 = zrrrow.createCell(0);
						cell2.setCellType(HSSFCell.CELL_TYPE_STRING);
						cell2.setCellValue((String) zrrdata.get(j).getA000001());

						cell2 = zrrrow.createCell(1);
						cell2.setCellType(HSSFCell.CELL_TYPE_STRING);
						cell2.setCellValue((String) zrrdata.get(j).getZJHM());

						cell2 = zrrrow.createCell(2);
						cell2.setCellType(HSSFCell.CELL_TYPE_STRING);
						cell2.setCellValue((String) zrrdata.get(j).getXZXDRLB());

						cell2 = zrrrow.createCell(3);
						cell2.setCellType(HSSFCell.CELL_TYPE_STRING);
						cell2.setCellValue("无数据");

						cell2 = zrrrow.createCell(4);
						cell2.setCellType(HSSFCell.CELL_TYPE_STRING);
						cell2.setCellValue(zrrdata.get(j).getZJLX());

						cell2 = zrrrow.createCell(5);
						cell2.setCellType(HSSFCell.CELL_TYPE_STRING);
						cell2.setCellValue(zrrdata.get(j).getCF_WSH());

						cell2 = zrrrow.createCell(6);
						cell2.setCellType(HSSFCell.CELL_TYPE_STRING);
						cell2.setCellValue(zrrdata.get(j).getWFXW());

						cell2 = zrrrow.createCell(7);
						cell2.setCellType(HSSFCell.CELL_TYPE_STRING);
						cell2.setCellValue(zrrdata.get(j).getWFSS());

						cell2 = zrrrow.createCell(8);
						cell2.setCellType(HSSFCell.CELL_TYPE_STRING);
						cell2.setCellValue(zrrdata.get(j).getCFYJ());

						cell2 = zrrrow.createCell(9);
						cell2.setCellType(HSSFCell.CELL_TYPE_STRING);
						cell2.setCellValue(zrrdata.get(j).getCFLB());

						cell2 = zrrrow.createCell(10);
						cell2.setCellType(HSSFCell.CELL_TYPE_STRING);
						cell2.setCellValue(zrrdata.get(j).getCFLB());

						cell2 = zrrrow.createCell(11);
						cell2.setCellType(HSSFCell.CELL_TYPE_STRING);
						cell2.setCellValue(zrrdata.get(j).getCFNR());

						cell2 = zrrrow.createCell(12);
						cell2.setCellType(HSSFCell.CELL_TYPE_STRING);
						cell2.setCellValue(zrrdata.get(j).getFKJE());

						cell2 = zrrrow.createCell(13);
						cell2.setCellType(HSSFCell.CELL_TYPE_STRING);
						cell2.setCellValue(zrrdata.get(j).getCF_MSSD());

						cell2 = zrrrow.createCell(14);
						cell2.setCellType(HSSFCell.CELL_TYPE_STRING);
						cell2.setCellValue(zrrdata.get(j).getCF_ZKDX());

						cell2 = zrrrow.createCell(15);
						cell2.setCellType(HSSFCell.CELL_TYPE_STRING);
						cell2.setCellValue(zrrdata.get(j).getCF_JDRQ());

						cell2 = zrrrow.createCell(16);
						cell2.setCellType(HSSFCell.CELL_TYPE_STRING);
						cell2.setCellValue(zrrdata.get(j).getCF_YXQ());

						cell2 = zrrrow.createCell(17);
						cell2.setCellType(HSSFCell.CELL_TYPE_STRING);
						cell2.setCellValue(zrrdata.get(j).getCF_GSJZQ());

						cell2 = zrrrow.createCell(18);
						cell2.setCellType(HSSFCell.CELL_TYPE_STRING);
						cell2.setCellValue(zrrdata.get(j).getCFJG());

						cell2 = zrrrow.createCell(19);
						cell2.setCellType(HSSFCell.CELL_TYPE_STRING);
						cell2.setCellValue(zrrdata.get(j).getCFJGSHXYDM());

						cell2 = zrrrow.createCell(20);
						cell2.setCellType(HSSFCell.CELL_TYPE_STRING);
						cell2.setCellValue(zrrdata.get(j).getCF_LYDW());

						cell2 = zrrrow.createCell(21);
						cell2.setCellType(HSSFCell.CELL_TYPE_STRING);
						cell2.setCellValue(zrrdata.get(j).getCF_LYDWDM());

						cell2 = zrrrow.createCell(22);
						cell2.setCellType(HSSFCell.CELL_TYPE_STRING);
						cell2.setCellValue(zrrdata.get(j).getBZ());
					}

					// 6.将其生成一个excel文件，输出
					String namezrr = absolutepath + File.separator + DateUtils.getCurrentTimeNumber() + "自然人行政处罚.xls";
					File filezrr = new File(namezrr);
					try {
						// 在本地生成一个excel文件，在传

						FileOutputStream file2 = new FileOutputStream(filezrr);
						zrrworkbook.write(file2);
						file2.close();
					} catch (UnsupportedEncodingException e) {
						e.printStackTrace();
					}
					FileItem fileItemzrr = createFileItem(filezrr, "自然人行政处罚");
					table.setRootId(100001);
					CommonsMultipartFile zrrfile = new CommonsMultipartFile(fileItemzrr);
					batchId = batchLogService.doUploadFile(table, zrrfile, userInfo, absolutepath);
					// 添加处理流程日志
					stepLogService.addStepLog(batchId, "文件上传完成,请耐心等待处理结果。");
					filezrr.delete();
				}
			}

		} catch (Exception e) {

			return new RestServiceResult<Object>("上传失败");
		}
		return new RestServiceResult<Object>("上传成功");
	}

	/**
	 * 判断Excel的版本,获取Workbook
	 * 
	 * @param in
	 * @param filename
	 * @return
	 * @throws IOException
	 */
	public static Workbook getWorkbok(File file) throws IOException {
		Workbook wb = null;
		FileInputStream in = new FileInputStream(file);
		if (file.getName().endsWith(EXCEL_XLS)) { // Excel 2003
			wb = new HSSFWorkbook(in);
		} else if (file.getName().endsWith(EXCEL_XLSX)) { // Excel 2007/2010
			wb = new XSSFWorkbook(in);
		}
		return wb;
	}

	/**
	 * 根据fileType不同读取excel文件
	 *
	 * @param path
	 * @param path
	 * @throws IOException
	 */
	@SuppressWarnings({ "resource", "deprecation" })
	public static List<List<String>> readExcel(MultipartFile file) {
		// String fileType = path.substring(path.lastIndexOf(".") + 1);
		// return a list contains many list

		// 获得文件名
		String fileName = file.getOriginalFilename();
		// 获取文件的后缀名 .xls/xlsx
		String suffix = fileName.substring(fileName.lastIndexOf("."));
		List<List<String>> lists = new ArrayList<List<String>>();
		// 读取excel文件
		Workbook wb = null;
		try {
			if (suffix.equals("xls")) {
				wb = new HSSFWorkbook(file.getInputStream());
			} else {
				wb = new XSSFWorkbook(file.getInputStream());
			}
			// 读取第一个工作页sheet
			Sheet sheet = wb.getSheetAt(0);
			// 第一行为标题
			for (Row row : sheet) {
				ArrayList<String> list = new ArrayList<String>();
				for (Cell cell : row) {
					// 根据不同类型"转化成字符串
					cell.setCellType(Cell.CELL_TYPE_STRING);
					list.add(cell.getStringCellValue());
				}
				lists.add(list);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (file.getInputStream() != null)
					file.getInputStream().close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
		return lists;
	}

	// 把File转化为CommonsMultipartFile
	public FileItem createFileItem(File file, String fieldName) {
		// DiskFileItemFactory()：构造一个配置好的该类的实例
		// 第一个参数threshold(阈值)：以字节为单位.在该阈值之下的item会被存储在内存中，在该阈值之上的item会被当做文件存储
		// 第二个参数data
		// repository：将在其中创建文件的目录.用于配置在创建文件项目时，当文件项目大于临界值时使用的临时文件夹，默认采用系统默认的临时文件路径
		FileItemFactory factory = new DiskFileItemFactory(16, null);
		// fieldName：表单字段的名称；第二个参数 ContentType；第三个参数isFormField；第四个：文件名
		FileItem item = factory.createItem(fieldName, "text/plain", true, file.getName());
		int bytesRead = 0;
		byte[] buffer = new byte[8192];
		FileInputStream fis = null;
		OutputStream os = null;
		try {
			fis = new FileInputStream(file);
			os = item.getOutputStream();
			while ((bytesRead = fis.read(buffer, 0, 8192)) != -1) {
				os.write(buffer, 0, bytesRead);// 从buffer中得到数据进行写操作
			}
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			try {
				if (os != null) {
					os.close();
				}
				if (fis != null) {
					fis.close();
				}
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
		return item;
	}

	/**
	 * 上传文件
	 *
	 * @param file
	 *            文件
	 * @param request
	 * @param orgBm
	 *            所属编码
	 * @param fileName
	 *            文件名称
	 * @param tableNameEn
	 *            文件英文名
	 * @throws IOException
	 * @throws IllegalStateException
	 */
	private void fileUpload(MultipartFile file, String pathFile, String batchId, String fileType)
			throws IllegalStateException, IOException {
		File uploadFile = null;

		File dirPath = new File(pathFile);
		uploadFile = new File(pathFile + File.separator + batchId + fileType);
		if (!dirPath.exists()) {
			uploadFile.mkdirs();
		}
		file.transferTo(uploadFile);

	}

}
